<?php

require_once(dirname(__FILE__).'/../../../../lib/adodb/adodb.inc.php');

/** Represents an ebook/item for the course. */
class item_info
{
    private $publiccode;
    private $title;
    private $editionnum;
    private $usedate;
    private $student;
    private $tutor;
    
    public function __construct($publiccode,$title,$editionnum,
        $usedate,$numperstudent,$numpertutor)
    {
        $this->publiccode=$publiccode;
        $this->title=$title;
        $this->editionnum=$editionnum;
        $this->usedate=$usedate;
        $this->student=trim($numperstudent)!='' && (int)trim($numperstudent)>0;
        $this->tutor=trim($numpertutor)!='' && (int)trim($numpertutor)>0;
    }
    
    /** @return Public code for the item (ISBN... or SUP...) */
    public function get_public_code() { return $this->publiccode; }
    
    /** @return Initial version of title, from database */
    public function get_title() { return $this->title; }
    
    /** @return Edition number (major version) of the item */
    public function get_edition_num() { return $this->editionnum; }
    
    /** @return Extended public code that includes edition number  */
    public function get_extended_public_code() { return $this->get_public_code().'/'.$this->get_edition_num(); }
    
    /** @return student use (mailing) date in Unix timestamp format, or 0 if no use date is available */
    public function get_use_date() { return $this->usedate; }
    
    /** @return True if this resource is sent to students */
    public function is_for_students() { return $this->student; }
    
    /** @return True if this resource is sent to tutors */
    public function is_for_tutors() { return $this->tutor; }
    
    public function debug_print() 
    { 
        print '<pre>[item_info] '.$this->publiccode.'/'.$this->editionnum.':'.$this->title.
            ' ('.date('Y-m-d',$this->usedate).')'.
            ($this->is_for_students() ? ' >students' : '') .
            ($this->is_for_tutors() ? ' >tutors' : '').
            '</pre>'; 
    }
}

class tracker_database {
    const DBSERVER='tracker.open.ac.uk';
    const DBNAME='ddx';
    const DBUSER='odbc_read';
    const DBPASS='';
    
    private $db;
    
    public function __construct() {
        $this->db=ADONewConnection('postgres');
        if(!$this->db) {
            throw new Exception('Failed to create Tracker database connection');
        }
        if(!$this->db->Connect(self::DBSERVER,self::DBUSER,self::DBPASS,self::DBNAME)) {
            throw new Exception('Failed to connect to Tracker database: '.$this->db->ErrorMsg());
        }
    }
    
    public function tracker_query($query) {
        // Just for testing
        $rs=$this->db->Execute($query);
        if(!$rs) {
            throw new Exception('Tracker query failed: '.$this->db->ErrorMsg()); 
        }
        $results=array();            
        while(!$rs->EOF)
        {
            $thing=$rs->fields;
            foreach($thing as $key=>$value) {
                if(is_numeric($key)) {
                    unset($thing[$key]);
                }
            }
            print_object((object)$thing);
            $rs->MoveNext();            
        }
        $rs->close();        
    }
    
    public function get_items($coursecode,$presentation,$virtualmailing=true) {
        // Make sure course code is upper-case and presentation is 4 or 5 letter version
        $coursecode=strtoupper($coursecode);        
        if(strlen($presentation)<4) {
            $presentation='20'.$presentation;
        }
        $presentation=strtoupper($presentation);
        
        // These two variants are used in the SQL to help dealing with comparisons
        // where one or other presentation doesn't have a letter on
        if(strlen($presentation)<5) {
            $presentationa=$presentation.'A';
            $presentationc=$presentation.'C';
        } else {        
            $presentationa=$presentationc=$presentation;
        }        
        
        /*
Explanation of query issues.        
        
We need first_use_pres only in order to link to things in Portfolio.

For presentations, a 2006 course could be starting in month A, B, or C (according
to a quick poll of the SDT people nextdoor) but not later. So for example if
there are resources marked as 06 or 06B then the 06 course should get them; 
if they're 06K then it shouldn't. That's what all the business with first_use_pres
and last_use_pres is for. Similarly, if a resource is marked as ending 06 then a course
marked as 06B will get it, but not one marked 06E. 

For item type,
A = main text, B = supp text, C = CAU-produced text (which they decided
 they don't want to include)
         */

        $presentationsql=$this->db->qstr($presentation);
        $coursecodesql=$this->db->qstr($coursecode);
        $presentationasql=$this->db->qstr($presentationa);
        $presentationcsql=$this->db->qstr($presentationc);
        
        $mailingcondition='';
        if($virtualmailing) {
            $mailingcondition="AND m.mailing_code_type='V'";
        }

        $query=<<<ENDQUERY
SELECT

    a.public_code,a.item_edition_reference,a.item_edition_num,  
    MAX(m.mailing_desp_date) AS maxusedate,  
    SUM(CAST(TRIM('0' || e.student_mailing_qty) AS Integer)) AS numperstudent, 
    SUM(CAST(TRIM('0' || e.tutor_mailing_qty) AS Integer)) AS numpertutor

FROM  
    imp_course_pres_mailings AS d
      
    INNER JOIN imp_mailings AS m ON 
      d.planet_mailing_id=m.planet_mailing_id
      
    INNER JOIN imp_mailing_lines AS e ON 
      d.planet_mailing_id=e.planet_mailing_id
      
    LEFT JOIN imp_assembly_edition_parts AS p ON 
      p.assembly_item_id=e.item_id 
      AND p.assembly_item_edition_num=e.item_edition_num
      
    INNER JOIN imp_item_editions AS a ON 
      a.item_id=COALESCE(p.part_item_id,e.item_id) 
      AND a.item_edition_num=COALESCE(p.part_item_edition_num,e.item_edition_num)  

    INNER JOIN imp_items AS c ON c.item_id=a.item_id
    
WHERE
    
    d.course_code=$coursecodesql   
    AND d.pres_code_5=$presentationsql
    $mailingcondition
    AND (a.public_code LIKE 'SUP%' OR a.public_code LIKE 'ISBN%' OR a.public_code LIKE 'N%' OR a.public_code LIKE 'WEB%')    
    AND (c.item_sub_type='A' or c.item_sub_type='B' or c.item_sub_type='K')

GROUP BY
    
    a.public_code,a.item_edition_reference,a.item_edition_num         
         
ORDER BY

    a.public_code,a.item_edition_num
         
ENDQUERY;
        
        $rs=$this->db->Execute($query);
        if(!$rs) {
            throw new Exception('Tracker query failed: '.$this->db->ErrorMsg()); 
        }
        $results=array();            
        while(!$rs->EOF)
        {            
            $results[] = new item_info(            
                $rs->fields['public_code'],
                $rs->fields['item_edition_reference'],
                $rs->fields['item_edition_num'],
                ((!$rs->fields['maxusedate']) ? 0 : $this->db->UnixDate($rs->fields['maxusedate'])),
                $rs->fields['numperstudent'],
                $rs->fields['numpertutor']);
            $rs->MoveNext();              
        }
                
        // If there are no items, check the database isn't completely empty - if
        // an overnight run failed, it may be
        if(count($results)==0)
        {
            $rs=$this->db->Execute('SELECT COUNT(*) AS cnt FROM imp_item_editions');        
            if($rs->fields['cnt']==0)
                throw new Exception('Database appears to be empty');
        }
        
        // Filter the return value to get rid of unnecessary items
        foreach($results as $key=>$item) {
            $itemcode=$item->get_extended_public_code();
            $matches=array();
            if(preg_match('/^WEB(.*)$/',$itemcode,$matches)) {
                // Exclude WEB items if there is a SUP item with the same code
                $searchcode='SUP'.$matches[1];
                foreach($results as $otheritem) {
                    if($otheritem->get_extended_public_code()==$searchcode) {
                        unset($results[$key]);
                        break;
                    }
                }
            }
        }
               
        return $results;
    }
}

if(preg_match('|/mod/resourcepage/extensions/etexts/tracker_database.php$|',$_SERVER['PHP_SELF'])) {
    require_once('../../../../config.php');
    require_login();
    if(!isadmin()) {
        error('No permission');
    }
    
    print '<html><head><title>Etext checker</title><body>';
    
    if(optional_param('course',0,PARAM_RAW)) {
        $course=strtoupper(required_param('course',PARAM_RAW));
        $pres=strtoupper(required_param('pres',PARAM_RAW));
        if(!preg_match('/^[A-Z0-9]+$/',$course) || !preg_match('/^[A-Z0-9]+$/',$pres)) {
            error('Invalid course/pres');
        }
        $virtual=optional_param('virtual',0,PARAM_INT) ? true : false;
        
        print '<h1>'.$course.'-'.$pres.($virtual?' virtual mailing items' : ' all mailing items').'</h1>';
        $test=new tracker_database();
        $items=$test->get_items($course,$pres,$virtual);
        
        print '<style type="text/css">th { text-align:left }</style>';
        print '<table cellspacing="8"><tr><th>Public code</th><th>Title</th><th>Access</th><th>Use date</th></tr>';
        foreach($items as $item) {
            print '<tr><td>'.$item->get_extended_public_code().
                '</td><td>'.htmlspecialchars($item->get_title()).
                '</td><td>'.(htmlspecialchars($item->is_for_students() ? 'Students' : 'Tutors')).
                '</td><td>'.htmlspecialchars(date('Y-m-d',$item->get_use_date())).
            		'</td></tr>';
        }
        print '</table>';
    }
    
?><h1>Etext checker</h1>
<p>This system shows you etexts for a particular course. Input boxes are not 
case-sensitive.</p>
<form action="tracker_database.php" method="get"><div>
<label>Course <input type="text" name="course" size="10" <?php if(!empty($course)) print 'value="'.htmlspecialchars($course).'"'; ?>/></label>
<label>Pres <input type="text" name="pres" size="3" <?php if(!empty($pres)) print 'value="'.htmlspecialchars($pres).'"'; ?>/></label><br />
<label><input type="checkbox" name="virtual" value="1" <?php if(!isset($virtual) || $virtual) print 'checked="checked"'?>/> Virtual mailing only</label><br/>
<input type="submit"/>
</div></form></body></html>        
<?php
    
    
}

?>